#!/use/bin/python
# coding=utf-8
# 日志项额外操作
import datetime
import pymysql

from dbutils.pooled_db import PooledDB



# proactive_service_conf 数据源
def getConfConnection():
    # 开发环境
    # pool = PooledDB(pymysql, 1, host='172.20.135.96', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_conf',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.150.109', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                db='proactive_service_conf', port=3307)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

# proactive_service_data 数据源
def getDataConnection():
    # 开发环境
    #pool = PooledDB(pymysql, 1, host='172.20.151.80', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_data',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                    db='proactive_service_data', port=3407)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

def pushMsgReceiver():
    try:
        connData, curData = getDataConnection()
        connConf, curConf = getConfConnection()
        # 查询日志项
        selectSql = f"""select t.active_id, JSON_EXTRACT(t.msg,'$.timestamp') timestamp from log_common_service_{yyMMddDate} t where t.key ='pushMsgReceiver' """
        curData.execute(selectSql)
        selectResult = curData.fetchall()
        for result in selectResult:
            activeId = result[0]
            timestamp = result[1]
            updateSql = f"update service_push_list t set t.push_status = 3 ,last_update_time = now() where  t.`timestamp` = {timestamp} and active_id = '{activeId}' and t.push_status != 4 and t.push_status != 3"
            #print(updateSql)
            curConf.execute(updateSql)
            connConf.commit()
        print("------------------- 接收push消息成功:执行结束 ----------------------")
    except Exception as e:
        print(e)
    finally:
        connData.close()
        curData.close()
        connConf.close()
        curConf.close()


def showQuestionnaire():
    try:
        connData, curData = getDataConnection()
        connConf, curConf = getConfConnection()
        # 查询日志项
        selectSql = f"""select t.active_id, JSON_EXTRACT(t.msg,'$.service') service  from log_common_service_{yyMMddDate} t where t.`key` ='showQuestionnaire' """
        curData.execute(selectSql)
        selectResult = curData.fetchall()
        for result in selectResult:
            activeId = result[0]
            service = result[1]
            updateSql = f"""update service_push_list t set t.push_status = 4 ,last_update_time = now() where 1=1 and active_id = '{activeId}'
                        and (select tt.service_id from service_push_task tt where tt.id = t.task_id ) =  (select id from lifeassistant_service ttt where ttt.service_key = {service}) and push_status!=4"""
            #print(updateSql)
            curConf.execute(updateSql)
            connConf.commit()
        print("------------------- 问卷弹窗成功:执行结束 ----------------------")
    except Exception as e:
        print(e)
    finally:
        connData.close()
        curData.close()
        connConf.close()
        curConf.close()

#如 2022年12月13号 获取 221213 作为分表后缀
def getdateyyMMdd(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y%m%d')
    re_date = re_date[2:]
    return re_date



if __name__ == '__main__':
    d = 1
    yyMMddDate = getdateyyMMdd(d)
    pushMsgReceiver()
    showQuestionnaire()
